
Imports System.Data
Imports System.Data.SqlClient


Namespace Cliente
Public Class Cliente

        Public Function Listar(ByVal ClienteId As Integer, ByVal vDocumento As String) As Entity.Cliente.Cliente

            Dim strNameProcedure As String = "up_Cliente_Cliente_obtener"
            Dim sqlReader As SqlDataReader = Nothing
            Dim sqlCon As SqlConnection = New SqlConnection(Configuracion.Connexion.Cnn)
            Dim objEnt As Entity.Cliente.Cliente
            Try
                sqlCon.Open()
                Dim sqlCmd As SqlCommand = New SqlCommand(strNameProcedure, sqlCon)
                With sqlCmd
                    .CommandType = CommandType.StoredProcedure
                    .Parameters.Add(New SqlParameter("@ClienteId", SqlDbType.Int)).Value = ClienteId
                    .Parameters.Add(New SqlParameter("@DocumentoNro", SqlDbType.VarChar, 10)).Value = vDocumento
                End With
                sqlReader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection)
                If sqlReader.HasRows Then
                    While sqlReader.Read
                        objEnt = New Entity.Cliente.Cliente
                        With objEnt
                            .ClienteId = sqlReader("ClienteId").ToString
                            .Nombre = sqlReader("Nombre").ToString
                            .DocumentoTipoId = sqlReader("DocumentoTipoId").ToString
                            .DocumentoNro = sqlReader("DocumentoNro").ToString
                            .Direccion = sqlReader("Direccion").ToString
                        End With
                    End While
                End If
            Catch ex As Exception
                Throw New Exception(ex.ToString)
            Finally
                sqlReader.Dispose()
                sqlReader.Close()
                sqlReader = Nothing
                sqlCon.Dispose()
                sqlCon.Close()
                sqlCon = Nothing
            End Try
            Return objEnt

        End Function


'***************************



public Function Listar(Opcion as string , _
		ClienteId as Integer, _
		Nombre as String, _
		DocumentoTipoId as Integer, _
		DocumentoNro as String, _
		Direccion as String) _
as  Entity.Cliente.ClienteCol

Dim strNameProcedure as string = "up_Cliente_Cliente_listar"
dim objCol as   entity.Cliente.ClienteCol
Try
Using conn As New SqlConnection(Configuracion.Connexion.Cnn)
Dim cmd As New SqlCommand(strNameProcedure, conn)
cmd.CommandType = CommandType.StoredProcedure
  cmd.Parameters.Add(New SqlParameter("@opcion", SqlDbType.varchar,50)).Value =Opcion
 cmd.Parameters.Add(New SqlParameter("@ClienteId",SqlDbType.INT)).Value =ClienteId
 cmd.Parameters.Add(New SqlParameter("@Nombre",SqlDbType.VARCHAR,150)).Value =Nombre
 cmd.Parameters.Add(New SqlParameter("@DocumentoTipoId",SqlDbType.INT)).Value =DocumentoTipoId
 cmd.Parameters.Add(New SqlParameter("@DocumentoNro",SqlDbType.VARCHAR,50)).Value =DocumentoNro
 cmd.Parameters.Add(New SqlParameter("@Direccion",SqlDbType.VARCHAR,150)).Value =Direccion
conn.Open()
Using dr As SqlDataReader = cmd.ExecuteReader()
		dim iClienteId as integer = dr.GetOrdinal("ClienteId")
		dim iNombre as integer = dr.GetOrdinal("Nombre")
		dim iDocumentoTipoId as integer = dr.GetOrdinal("DocumentoTipoId")
		dim iDocumentoNro as integer = dr.GetOrdinal("DocumentoNro")
		dim iDireccion as integer = dr.GetOrdinal("Direccion")

 Dim colCount As Integer = dr.FieldCount
 Dim values As Object() = New Object(colCount - 1) {}

 objCol =  New entity.Cliente.ClienteCol
  If dr.HasRows Then
While dr.Read()
dim objEnt =  new entity.Cliente.Cliente
dr.GetValues(values)

		objEnt.ClienteId = values(iClienteId)
		objEnt.Nombre = values(iNombre)
		objEnt.DocumentoTipoId = values(iDocumentoTipoId)
		objEnt.DocumentoNro = values(iDocumentoNro)
		objEnt.Direccion = values(iDireccion)
 objCol.Add(objEnt)
 End While
 End if
 End Using
 End Using
 Catch ex As Exception
Throw New Exception(ex.ToString)
Finally
 End Try
return objCol

End Function


'***************************


Public Shared Function Insert(ByVal opcion As String, _
		ClienteId as Integer, _
		Nombre as String, _
		DocumentoTipoId as Integer, _
		DocumentoNro as String, _
		Direccion as String) _
 As Integer

 dim strNameProcedure as string ="Up_Crm_Cliente_Insertar"
 Try
Dim intCodigo As Integer = -1
 Using sqlCon As SqlConnection = New SqlConnection(Configuracion.Connexion.Cnn)
Using sqlCmd As SqlCommand = New SqlCommand(strNameProcedure, sqlCon)
With sqlCmd
 .CommandType = CommandType.StoredProcedure
 .Parameters.Add(New SqlParameter("@opcion", SqlDbType.VarChar, 120)).Value = opcion
 .Parameters.Add(New SqlParameter("@ClienteId",SqlDbType.INT)).Value =ClienteId
 .Parameters.Add(New SqlParameter("@Nombre",SqlDbType.VARCHAR,150)).Value =Nombre
 .Parameters.Add(New SqlParameter("@DocumentoTipoId",SqlDbType.INT)).Value =DocumentoTipoId
 .Parameters.Add(New SqlParameter("@DocumentoNro",SqlDbType.VARCHAR,50)).Value =DocumentoNro
 .Parameters.Add(New SqlParameter("@Direccion",SqlDbType.VARCHAR,150)).Value =Direccion
.Parameters.Add(New SqlParameter("@variable_out", SqlDbType.Int))
 .Parameters("@variable_out").Direction = ParameterDirection.Output
 sqlCon.Open()
 .ExecuteNonQuery()
 intCodigo = .Parameters("@variable_out").Value
  Return intCodigo
  End With
 End Using
 End Using
 Catch ex As Exception
  Throw New Exception(ex.Message)
 Return -1
 End Try

End Function


'***************************


Public Shared Function Update(ByVal opcion As String, _
		ClienteId as Integer, _
		Nombre as String, _
		DocumentoTipoId as Integer, _
		DocumentoNro as String, _
		Direccion as String) _
 As Integer

 dim strNameProcedure as string ="Up_Crm_Cliente_Actualizar"
 Try
Dim intCodigo As Integer = -1
 Using sqlCon As SqlConnection = New SqlConnection(Configuracion.Connexion.Cnn)
Using sqlCmd As SqlCommand = New SqlCommand(strNameProcedure, sqlCon)
With sqlCmd
 .CommandType = CommandType.StoredProcedure
 .Parameters.Add(New SqlParameter("@opcion", SqlDbType.VarChar, 120)).Value = opcion
 .Parameters.Add(New SqlParameter("@ClienteId",SqlDbType.INT)).Value =ClienteId
 .Parameters.Add(New SqlParameter("@Nombre",SqlDbType.VARCHAR,150)).Value =Nombre
 .Parameters.Add(New SqlParameter("@DocumentoTipoId",SqlDbType.INT)).Value =DocumentoTipoId
 .Parameters.Add(New SqlParameter("@DocumentoNro",SqlDbType.VARCHAR,50)).Value =DocumentoNro
 .Parameters.Add(New SqlParameter("@Direccion",SqlDbType.VARCHAR,150)).Value =Direccion
.Parameters.Add(New SqlParameter("@variable_out", SqlDbType.Int))
 .Parameters("@variable_out").Direction = ParameterDirection.Output
 sqlCon.Open()
 .ExecuteNonQuery()
 intCodigo = .Parameters("@variable_out").Value
  Return intCodigo
  End With
 End Using
 End Using
 Catch ex As Exception
  Throw New Exception(ex.Message)
 Return -1
 End Try

End Function


'***************************


Public Shared Function Eliminar(ByVal opcion As String, _
		ClienteId as Integer, _
		Nombre as String, _
		DocumentoTipoId as Integer, _
		DocumentoNro as String, _
		Direccion as String) _
 As Integer

 dim strNameProcedure as string ="Up_Crm_Cliente_Eliminar"
 Try
Dim intCodigo As Integer = -1
 Using sqlCon As SqlConnection = New SqlConnection(Configuracion.Connexion.Cnn)
Using sqlCmd As SqlCommand = New SqlCommand(strNameProcedure, sqlCon)
With sqlCmd
 .CommandType = CommandType.StoredProcedure
 .Parameters.Add(New SqlParameter("@opcion", SqlDbType.VarChar, 120)).Value = opcion
                .Parameters.Add(New SqlParameter("@ClienteId", SqlDbType.Int)).Value =ClienteId
.Parameters.Add(New SqlParameter("@variable_out", SqlDbType.Int))
 .Parameters("@variable_out").Direction = ParameterDirection.Output
 sqlCon.Open()
 .ExecuteNonQuery()
 intCodigo = .Parameters("@variable_out").Value
  Return intCodigo
  End With
 End Using
 End Using
 Catch ex As Exception
  Throw New Exception(ex.Message)
 Return -1
 End Try

End Function


'***************************



End Class



end namespace

